﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Text;
using System.Windows.Forms;
using DevExpress.XtraEditors;
using System.Linq;
using System.Net;
using System.Net.Mail;
using System.IO;
using Excel;
using DevExpress.XtraReports;
using DevExpress.XtraReports.UI;
namespace Exact_Extension
{
    public partial class SendEmailForm : DevExpress.XtraEditors.XtraForm
    {
        ExactDataContext DS;
        IFormatProvider culture = new System.Globalization.CultureInfo("fr-FR", true);
        public SendEmailForm(ExactDataContext _DS)
        {
            InitializeComponent();
            DS = _DS;
            this.fromdate.EditValue = DateTime.Now.Date;
            this.todate.EditValue = DateTime.Now.Date;
            this.txtSubject.EditValue = "<<count>> reminder letter for Student <<StudentID>>  <<StudentName>> , Invoice Number : <<InvoiceNo>>";
            this.txtBCC.EditValue = "Accounting@ishcmc.edu.vn";
            this.LoadReminderTemplateAAVN();
            this.LoadReminderTemplateISHCMC();
        }
        private void LoadReminderTemplateISHCMC()
        {
            FileInfo fileTemplate = new FileInfo("EmailTemplate\\ReminderLetterCOISHCMC.docx");
            this.richEditControl_FirstCO.LoadDocument(fileTemplate.OpenRead(), DevExpress.XtraRichEdit.DocumentFormat.OpenXml);

            FileInfo fileTemplate1 = new FileInfo("EmailTemplate\\ReminderLetterSecondCO.docx");
            this.richEditControl_SecondCO.LoadDocument(fileTemplate1.OpenRead(), DevExpress.XtraRichEdit.DocumentFormat.OpenXml);

            FileInfo fileTemplate2 = new FileInfo("EmailTemplate\\ReminderLetterINVISHCMC.docx");
            this.richEditControl_FirstFA.LoadDocument(fileTemplate2.OpenRead(), DevExpress.XtraRichEdit.DocumentFormat.OpenXml);

            FileInfo fileTemplate3 = new FileInfo("EmailTemplate\\ReminderLetterSecondInv.docx");
            this.richEditControl_SecondFA.LoadDocument(fileTemplate3.OpenRead(), DevExpress.XtraRichEdit.DocumentFormat.OpenXml);
        }
        private void LoadReminderTemplateAAVN()
        {
        
                FileInfo fileTemplate = new FileInfo("EmailTemplate\\ReminderLetterINVAAVN.docx");
                this.richEditControl_AAReminderCOFirst.LoadDocument(fileTemplate.OpenRead(), DevExpress.XtraRichEdit.DocumentFormat.OpenXml);
          
                FileInfo fileTemplate1 = new FileInfo("EmailTemplate\\ReminderLetterSecondCO.docx");
                this.richEditControl_AAReminderCOSecond.LoadDocument(fileTemplate1.OpenRead(), DevExpress.XtraRichEdit.DocumentFormat.OpenXml);

              
                    FileInfo fileTemplate2 = new FileInfo("EmailTemplate\\ReminderLetterINVAAVN.docx");
                    this.richEditControl_AAReminderFAFirst.LoadDocument(fileTemplate2.OpenRead(), DevExpress.XtraRichEdit.DocumentFormat.OpenXml);
               
                    FileInfo fileTemplate3 = new FileInfo("EmailTemplate\\ReminderLetterSecondInv.docx");
                    this.richEditControl_AAReminderFASecond.LoadDocument(fileTemplate3.OpenRead(), DevExpress.XtraRichEdit.DocumentFormat.OpenXml);
              
         
        }
        private List<ISHCMC_ARReminder> GetListARReminder(string filenames)
        {
            List<ISHCMC_ARReminder> ListAR = new List<ISHCMC_ARReminder>();
            FileInfo fileInfo = new FileInfo(filenames);
            FileStream stream = File.Open(this.openFileDialog1.FileName, FileMode.Open, FileAccess.Read);
            IExcelDataReader excelReader = null;
            if (fileInfo.Extension == ".xls")
            {
                //1. Reading from a binary Excel file ('97-2003 format; *.xls)
                excelReader = ExcelReaderFactory.CreateBinaryReader(stream);
            }
            if (fileInfo.Extension == ".xlsx")
            {
                //...
                //2. Reading from a OpenXml Excel file (2007 format; *.xlsx)
                excelReader = ExcelReaderFactory.CreateOpenXmlReader(stream);
            }
            //...

            //4. DataSet - Create column names from first row
            excelReader.IsFirstRowAsColumnNames = true;
            DataSet result = excelReader.AsDataSet();
            int i = 0;
            //5. Data Reader methods
            while (excelReader.Read())
            {
                if (i > 0)
                {
                    ISHCMC_ARReminder itemAR = new ISHCMC_ARReminder();
                    itemAR.Exact_Student_ID = excelReader.IsDBNull(0) ? "" : excelReader.GetValue(0).ToString();
                    itemAR.Student_ID = excelReader.IsDBNull(1) ? "" : excelReader.GetValue(1).ToString();
                    itemAR.Student_Name = excelReader.IsDBNull(2) ? "" : excelReader.GetValue(2).ToString();
                    itemAR.Payercode = excelReader.IsDBNull(3) ? "" : excelReader.GetValue(3).ToString();
                    itemAR.Payer_Name = excelReader.IsDBNull(4) ? "" : excelReader.GetValue(4).ToString();
                    itemAR.Classification = excelReader.IsDBNull(5) ? "" : excelReader.GetValue(5).ToString();
                    itemAR.Grade = excelReader.IsDBNull(6) ? "" : excelReader.GetValue(6).ToString();
                    if (!excelReader.IsDBNull(7))
                    {
                        itemAR.Enrollment_Date = Convert.ToDateTime(excelReader.GetValue(7));
                    }
                    itemAR.School = excelReader.IsDBNull(8) ? "" : excelReader.GetValue(8).ToString();
                    if (!excelReader.IsDBNull(9))
                    {
                        itemAR.Invoice_Date = Convert.ToDateTime(excelReader.GetValue(9));
                    }
                    itemAR.Proforma_Inv_No = excelReader.IsDBNull(10) ? "" : excelReader.GetValue(10).ToString();
                    if (!excelReader.IsDBNull(11))
                    {
                        itemAR.Due_Date = Convert.ToDateTime(excelReader.GetValue(11));
                    }
                    itemAR.Payment_Option = excelReader.IsDBNull(12) ? "" : excelReader.GetValue(12).ToString();
                    itemAR.Paid_Amount = excelReader.IsDBNull(13) ? 0 : Convert.ToDouble(excelReader.GetValue(13));
                    itemAR.Outstanding = excelReader.IsDBNull(14) ? 0 : Convert.ToDouble(excelReader.GetValue(14));
                    itemAR.TotalBillNet = excelReader.IsDBNull(15) ? 0 : Convert.ToDouble(excelReader.GetValue(15));
                    itemAR.Interest = excelReader.IsDBNull(16) ? 0 : Convert.ToDouble(excelReader.GetValue(16));
                    itemAR.Counting = excelReader.IsDBNull(17) ? 0 : Convert.ToDouble(excelReader.GetValue(17));
                    ListAR.Add(itemAR);
                }
                i++;

            }

            //6. Free resources (IExcelDataReader is IDisposable)
            excelReader.Close();
            return ListAR;
        }
        private void btnLoad_ItemClick(object sender, DevExpress.XtraBars.ItemClickEventArgs e)
        {

            DateTime FromDate = Convert.ToDateTime(this.fromdate.EditValue,culture);
            DateTime ToDate = Convert.ToDateTime(this.todate.EditValue,culture);
            if (this.txtFileName.EditValue != null)
            {
                List<ISHCMC_ARReminder> ListAR = this.GetListARReminder(this.txtFileName.EditValue.ToString());
                List<ISHCMC_V_StudentListWithDebtor_Ext> ListStudent = new List<ISHCMC_V_StudentListWithDebtor_Ext>();
                foreach (ISHCMC_ARReminder itemAR in ListAR)
                {
                    if (itemAR.Outstanding > 0)
                    {
                        var queryStudent = from ST in DS.ISHCMC_V_StudentListWithDebtor_Exts
                                           where ST.StudentID == itemAR.Student_ID
                                           select ST;
                        if (queryStudent.Count() > 0)
                        {
                            ISHCMC_V_StudentListWithDebtor_Ext itemStudent = queryStudent.First();
                            itemStudent.InvoiceNo = itemAR.Proforma_Inv_No;
                            itemStudent.Outstanding = Convert.ToDouble(itemAR.Outstanding.Value);
                            if (itemAR.Counting == 1)
                            {
                                itemStudent.CountingTimes = "First";

                            }
                            else if (itemAR.Counting == 2)
                            {
                                itemStudent.CountingTimes = "Second";
                            }
                            itemStudent.TotalOutstanding = itemAR.Outstanding.Value + itemAR.Interest.Value;
                            itemStudent.Interest = itemAR.Interest.Value;
                            ListStudent.Add(itemStudent);
                        }
                    }
                }
                this.iSHCMCVStudentListWithDebtorExtBindingSource.DataSource = ListStudent;
            }
            else
            {
                MessageBox.Show("Please select file excel list student to send reminder");
            }
        }

        private void btnSend_ItemClick(object sender, DevExpress.XtraBars.ItemClickEventArgs e)
        {
            this.SendEmailContent();
        }
        private void SendEmailContent()
        {
            foreach (int i in this.gridView_Student.GetSelectedRows())
            {
                
                ISHCMC_V_StudentListWithDebtor_Ext itemStudent = (ISHCMC_V_StudentListWithDebtor_Ext)this.gridView_Student.GetRow(i);
                //itemStudent.Email1 = "dbaadmin@ishcmc.edu.vn";
                //itemStudent.Email2 = "nguyenphuoctan@gmail.com";
                //itemStudent.EmailBilling = "tan.nguyen@cognitaschools.sg";
                string html = "";
                if (itemStudent.School == "ISHCMC")
                {
                    if (itemStudent.PayeeType == "CO")
                    {
                        if (itemStudent.CountingTimes == "First")
                        {
                            html = this.richEditControl_FirstCO.HtmlText;
                        }
                        else
                        {
                            html = this.richEditControl_SecondCO.HtmlText;
                        }
                        


                    }
                    else
                    {
                        if (itemStudent.CountingTimes == "First")
                        {

                            html = this.richEditControl_FirstFA.HtmlText;
                        }
                        else
                        {
                            html = this.richEditControl_SecondFA.HtmlText;
                        }

                      

                    }
                }
                else
                {
                    if (itemStudent.PayeeType == "CO")
                    {
                        if (itemStudent.CountingTimes == "First")
                        {
                            html = this.richEditControl_AAReminderCOFirst.HtmlText;
                        }
                        else
                        {
                            html = this.richEditControl_AAReminderCOSecond.HtmlText;
                        }
                       

                    }
                    else
                    {
                        if (itemStudent.CountingTimes == "First")
                        {
                            html = this.richEditControl_AAReminderFAFirst.HtmlText;
                        }
                        else
                        {
                            html = this.richEditControl_AAReminderFASecond.HtmlText;
                        }

                      

                    }
                }
                
                if (itemStudent.CountingTimes == "First")
                {
                    html = html.Replace("IT-Date", DateTime.Now.Date.ToString("dd MMMM yyyy"));
                    html = html.Replace("IT-StudentName", itemStudent.StudentName);
                    html = html.Replace("IT-Company", itemStudent.InvoiceDebtorName);
                    html = html.Replace("IT-Attention", itemStudent.Attention);
                    html = html.Replace("IT-Address", itemStudent.BillingAddress1 + " " + itemStudent.BillingAddress2 + " " + itemStudent.BillingAddress3);
                    html = html.Replace("IT-Email", itemStudent.Email1 + " ; " + itemStudent.Email2 + " ; " + itemStudent.EmailBilling);
                    html = html.Replace("IT-Parent", itemStudent.FamilyName);
                    html = html.Replace("IT-InvoiceNo", itemStudent.InvoiceNo);
                    html = html.Replace("IT-OutstandingAmount", itemStudent.Outstanding.Value.ToString("#,#") + "  VND");
                }
                if (itemStudent.CountingTimes == "Second")
                {
                    html = html.Replace("IT-CurrentDate", DateTime.Now.Date.ToString("dd MMMM yyyy"));
                    html = html.Replace("IT-StudentName", itemStudent.StudentName);
                    html = html.Replace("IT-Company", itemStudent.InvoiceDebtorName);
                    html = html.Replace("IT-Attention", itemStudent.Attention);
                    html = html.Replace("IT-Address", itemStudent.BillingAddress1 + " " + itemStudent.BillingAddress2 + " " + itemStudent.BillingAddress3);
                    html = html.Replace("IT-Email", itemStudent.Email1 + " ; " + itemStudent.Email2 + " ; " + itemStudent.EmailBilling);
                    html = html.Replace("IT-Parent", itemStudent.FamilyName);
                    html = html.Replace("IT-InvoiceNo", itemStudent.InvoiceNo);
                    html = html.Replace("IT-OutstandingAmount", itemStudent.Outstanding.Value.ToString("#,#") + "  VND");
                    html = html.Replace("IT-DateFirstReminder", "22 July 2013");
                    html = html.Replace("IT-TotalAmount", itemStudent.TotalOutstanding.Value.ToString("#,#") + " VND");
                    html = html.Replace("IT-AmountOutstanding", itemStudent.Outstanding.Value.ToString("#,#") + " VND");
                    html = html.Replace("IT-AmountInterest", itemStudent.Interest.Value.ToString("#,#") + " VND");
                    html = html.Replace("IT-DateDue", DateTime.Now.Date.AddDays(10).ToString("dd MMMM yyyy"));
                }

               
                this.sendEMailThroughHotMail(html, itemStudent);
                int CountSecond = 0;
                while (CountSecond < 10000)
                {
                    CountSecond++;
                }
            }
           
        }
        private void gridView_Student_FocusedRowChanged(object sender, DevExpress.XtraGrid.Views.Base.FocusedRowChangedEventArgs e)
        {

            

            
        }

        //method to send email to HOTMAIL
        public void sendEMailThroughHotMail(string contentHTML,ISHCMC_V_StudentListWithDebtor_Ext itemStudent)
        {
            try
            {
                string emailAccount = System.Configuration.ConfigurationSettings.AppSettings["AccountEmail"];
                string password = System.Configuration.ConfigurationSettings.AppSettings["AccountEmailPass"];
                string smtpserver = System.Configuration.ConfigurationSettings.AppSettings["SMTPServer"];
                string smtpport = System.Configuration.ConfigurationSettings.AppSettings["SMTPPort"];
                //Mail Message
                MailMessage mM = new MailMessage();
                //Mail Address
                mM.From = new MailAddress(emailAccount);
                //receiver email id
                if (itemStudent.Email1.Length > 5)
                {
                    mM.To.Add(itemStudent.Email1);
                }
                if (itemStudent.Email2.Length > 5)
                {
                    mM.To.Add(itemStudent.Email2);
                }
                if (itemStudent.EmailBilling.Length > 5)
                {
                    mM.To.Add(itemStudent.EmailBilling);
                }
              
                if (this.txtBCC.EditValue != null)
                {
                    mM.Bcc.Add(this.txtBCC.EditValue.ToString());
                }
                mM.Bcc.Add(emailAccount);
                if (this.txtCC.EditValue != null)
                {
                    mM.CC.Add(this.txtCC.EditValue.ToString());
                }
                //subject of the email
                mM.Subject = this.txtSubject.EditValue.ToString();
                mM.Subject = mM.Subject.Replace("<<StudentID>>" , itemStudent.StudentID);
                mM.Subject = mM.Subject.Replace("<<StudentName>>" , itemStudent.StudentName);
                mM.Subject = mM.Subject.Replace("<<InvoiceNo>>" , itemStudent.InvoiceNo);
                mM.Subject = mM.Subject.Replace("<<count>>", itemStudent.CountingTimes);
                //deciding for the attachment
                contentHTML = contentHTML.Replace("<body>","<body><img src=\"cid:Pic1\" >");
                contentHTML = contentHTML.Replace("</body>", "<body><img src=\"cid:Pic2\"></body>");
                contentHTML = contentHTML.Replace("&lt;&lt;ImgSignature&gt;&gt;", "<img src=\"cid:Pic3\">");
                AlternateView avHtml = AlternateView.CreateAlternateViewFromString(contentHTML, null, System.Net.Mime.MediaTypeNames.Text.Html);
                LinkedResource pic1 = new LinkedResource("PageHeader.jpg", System.Net.Mime.MediaTypeNames.Image.Jpeg);
                pic1.ContentId = "Pic1";
                avHtml.LinkedResources.Add(pic1);




                LinkedResource pic2 = new LinkedResource("PageFooter.jpg", System.Net.Mime.MediaTypeNames.Image.Jpeg);
                pic2.ContentId = "Pic2";
                avHtml.LinkedResources.Add(pic2);


                LinkedResource pic3 = new LinkedResource("VuSignature.jpg", System.Net.Mime.MediaTypeNames.Image.Jpeg);
                pic3.ContentId = "Pic3";
                avHtml.LinkedResources.Add(pic3);

                mM.AlternateViews.Add(avHtml);

               
               
              
                //SMTP client
                SmtpClient sC = new SmtpClient(smtpserver);
                //port number for Hot mail
                sC.Port = Convert.ToInt32(smtpport);
                //credentials to login in to hotmail account

                sC.Credentials = new NetworkCredential(emailAccount,password);
                //enabled SSL
                sC.EnableSsl = true;

                //Send an email
                if (mM.To.Count > 0)
                {
                    sC.Send(mM);


                    string _SentTo = "";
                    foreach (MailAddress itemSentTo in mM.To)
                    {
                        _SentTo += itemSentTo.Address + ";";
                    }
                    string _CC = "";
                    foreach (MailAddress itemCC in mM.CC)
                    {
                        _CC += itemCC.Address + ";";
                    }
                    string _BCC = "";
                    foreach (MailAddress itemBCC in mM.Bcc)
                    {
                        _BCC += itemBCC.Address + ";";
                    }

                    ISHCMC_EmailTracking emailTracking = new ISHCMC_EmailTracking
                    {
                        InvoiceNo = itemStudent.InvoiceNo,
                        StudentID = itemStudent.StudentID,
                        CC = _CC,
                        SentTo = _SentTo,
                        BCC = _BCC,
                        SentDateTime = DateTime.Now,
                        Subject = mM.Subject,
                        FromTo = mM.From.Address,
                        Attachment = contentHTML




                    };
                    DS.ISHCMC_EmailTrackings.InsertOnSubmit(emailTracking);
                    DS.SubmitChanges();
                }
            }//end of try block
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }//end of catch
        }

        private void timer1_Tick(object sender, EventArgs e)
        {

        }

        private void btnBrowse_ItemClick(object sender, DevExpress.XtraBars.ItemClickEventArgs e)
        {
            DialogResult result = this.openFileDialog1.ShowDialog();
            if (result == DialogResult.OK)
            {
                this.txtFileName.EditValue = this.openFileDialog1.FileName;
            }
        }

        
    }
}